/*
Purpose: To get all state in this database
*/
drop PROCEDURE [Person].[usp_GetAllStates]
go 
create PROCEDURE [Person].[usp_GetAllStates]
AS
BEGIN     
    select * from Person.StateProvince ;     
END;

go


/*
Purpose: To get all state in specific country
*/ 
drop PROCEDURE [Person].[usp_GetStatesInCountry]
go 
create PROCEDURE [Person].[usp_GetStatesInCountry]
    @CountryCode nchar(3)  
AS
BEGIN     
    select * from Person.StateProvince 
     where  CountryRegionCode=@CountryCode;
END;

go 
 


/*
Purpose: To get all state in specific country, 
   but only output brief info of one state, such as StateProvinceID, StateProvinceCode, Name
*/ 
drop PROCEDURE [Person].[usp_GetStateBriefsInCountry]
go 
create PROCEDURE [Person].[usp_GetStateBriefsInCountry]
    @CountryCode nchar(3)  
AS
BEGIN     
    select StateProvinceID, StateProvinceCode, CountryRegionCode, Name from Person.StateProvince 
    where CountryRegionCode=@CountryCode;
END;


go


/*
Purpose: To get all state in specific country, 
   but only output brief info of one state, such as StateProvinceID, StateProvinceCode, StateProvinceName
*/ 
drop PROCEDURE [Person].[usp_GetStateBriefsInCountry_2]
go 
create PROCEDURE [Person].[usp_GetStateBriefsInCountry_2]
    @CountryCode nchar(3)  
AS
BEGIN     
    select StateProvinceID, StateProvinceCode, CountryRegionCode, Name as StateProvinceName from Person.StateProvince 
    where  CountryRegionCode=@CountryCode;
END;


go
 
/*
Purpose: To update one country region  
*/ 
drop PROCEDURE [Person].[usp_UpdateCountryRegion]
go 
create PROCEDURE [Person].[usp_UpdateCountryRegion]
    @CountryRegionCode nchar(3), @Name nchar(50) 
AS
BEGIN     
    Delete from [Person].[CountryRegion] where CountryRegionCode=@CountryRegionCode;
    Insert into [Person].[CountryRegion] (CountryRegionCode,Name) values (@CountryRegionCode,@Name); 
END;

 
